package com.roc.web.action.merinfo;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;

public class MerinfoExportSearchCondtion extends BaseSearchCondition  {

	
	private String 	crtMinDate; // 创建开始时间(年月日)
	private String 	crtMaxDate; // 创建结束时间(年月日)
	

	private String merchantNo;//商户号
	private String merchantName;//商户简称
	private String  merBrName; //注册名称
	private String  certifName;//法人
	private String  cardNo; //法人身份证
	private String  contactName;//业务联系人
	private String  phone;//联系电话 
	private String  accountNo;//账号 
	private String  accountName;//账号 
	private String clearCardId; //结算人身份证
	private String directlyAgent; //直属代理商
	private String status;//状态
	private String association;//关联关系
	private String  parentCode;//父类

	private String  minDate;//入网时间
	private String  maxDate;//入网时间

	private String orgCode;// 机构号
	
	private String obj;//代理商id
	
	private String upMccCd ;// 商户MCC(42)
	private String busiLiceNo ;// 营业执照号码
	private String busLicCode ;// 审核人员Id 
	
	private String 	areaCode; // 区域id
	private String 	sonCode; // 一级代理商id
	

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getOrderPartOfHQL()
	 */
	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		
		return "   ";
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getRowCountHQL()
	 */
	@Override
	protected String getRowCountHQL() {// 48502000   85278888
		StringBuffer sb=new StringBuffer();

		sb.append( " SELECT count(f.mcht_cd)"); 
	    sb.append( "   FROM TBL_TERM_INF T, TBL_MCHT_INF F,tbl_mcht_bankaccount b ,T_OPERATORS op ");
	    sb.append( " WHERE T.MCHT_CD = F.MCHT_CD ");
		sb.append( "   AND f.mcht_cd= b.owner_cd ");
	//	sb.append( "   AND F.BANK_BELONG_CD = '48502000' ");
		sb.append( "   AND F.BANK_BELONG_CD = '" + orgCode + "' ");
		sb.append( "   AND op.id= TO_NUMBER(f.EXT1) ");
		
		return sb.toString();
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getSearchHQL()
	 */
	@Override
	protected String getSearchHQL() {
		StringBuffer sb=new StringBuffer();

		sb.append( " SELECT f.mcht_cd , "); // 商户号,
//		sb.append( "    '48500000' , "); //  所属机构,
//		sb.append( "    '1' , "); // 商户来源渠道,
//		sb.append( "     '代理商' , "); // 发展人推荐,
		sb.append( "    f.bank_belong_cd , "); // 所属收单机构,
	    sb.append( "     f.uc_bc_cd_32  , "); //收单机构32域,
//	    sb.append( "      '850' , "); // 商户前三位机构代码,
	    sb.append( "   (CASE WHEN trim(f.mchtcd_area_cd)IS NULL THEN f.prov_cd||f.city_cd ELSE f.mchtcd_area_cd  END) , "); // 商户号地区码,MCHTCD_AREA_CD
	    sb.append( "    f.up_mcc_cd , "); //,MCC
//	    sb.append( "    '是' , "); // 商户是否银联标准,
	    sb.append( "    f.name , "); // 商户工商注册名称,
	    sb.append( "    f.name_busi , "); // 营业名称,
	    sb.append( "    f.busi_lice_no , "); // 商户执照号,
	    sb.append( "    f.busi_rang , "); // 经营范围,
	    sb.append( "    f.busi_main , "); // 主营业务,
	    sb.append( "    f.certif , "); // 法人代表,
	    sb.append( "    f.certif_type , "); // 法人证件类型,
	    sb.append( "    f.certif_no , "); // 法人证件号,
	    sb.append( "    f.contact_name , "); // 商户联系人姓名,
	    sb.append( "    f.contact_phoneno , "); // 商户联系电话号码,
//	    sb.append( "    'CHN' , "); // 商户注册国家编码,
	    sb.append( "    f.prov_cd , "); // 省代码,
	    sb.append( "    f.city_cd , "); // 市代码,
	    sb.append( "    f.area_cd , "); // 县代码,
	    sb.append( "    f.reg_addr , "); // 地址,
	    sb.append( "    b.name bName, "); // 结算账户名称,
	    sb.append( "    b.account , "); // 结算账户,
	    sb.append( "     b.bank_name , "); // 结算账户开户行,
	    sb.append( "    b.bank_code , "); // 结算账户银行联行号,
	    sb.append( "    t.term_id , "); // 银联终端号
	    
	    sb.append( "    f.STLM_WAY_DESC , "); // 商户非法人身份证
	    sb.append( "    OP.real_name , "); // 直属代理商
	    
	    sb.append( " ( select ta.name from tbl_area ta where ta.id  = op.companyid ) companyName , "); // 所属分公司
	    sb.append( " ( select t2.real_name from t_operators t2 where t2.role_type='02' and t2.oneagentid = op.oneagentid ) oneAgentName, "); // 一级代理商
	    sb.append( "  ( select t3.real_name from t_operators t3 where t3.id = f.ori_chnl_desc ) checkName "); // 审核人员

	    sb.append( "   FROM TBL_TERM_INF T, TBL_MCHT_INF F,tbl_mcht_bankaccount b ,T_OPERATORS op  ");
	    sb.append( " WHERE T.MCHT_CD = F.MCHT_CD ");
		sb.append( "   AND f.mcht_cd= b.owner_cd ");
//		sb.append( "   AND F.BANK_BELONG_CD = '48502000' ");
		sb.append( "   AND F.BANK_BELONG_CD = '" + orgCode + "' ");
		sb.append( "   AND op.id= TO_NUMBER(f.EXT1) ");
//		System.out.println(sb.toString());
		return sb.toString();
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getWhereHQLFieldBeanList()
	 */
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		
		if (Constance.isNotEmpty(crtMinDate) && Constance.isNotEmpty(crtMaxDate)) {
			//Date startDate = Constance.formateDate(minDate);
			//Date endDate = Constance.formateDate(maxDate);
			String startDate = crtMinDate.replace("-", "");
			String endDate = crtMaxDate.replace("-", "");
			filedBeanList.add(new WhereHQLFieldBean("to_char(f.rec_crt_ts,'yyyyMMdd')", startDate,
					">=", false));
			filedBeanList.add(new WhereHQLFieldBean("to_char(f.rec_crt_ts,'yyyyMMdd')", endDate,
					"<=", false));
		}
		
		//商户非法人身份证
		if(Constance.isNotEmpty(clearCardId)){
			filedBeanList.add(new WhereHQLFieldBean("f.STLM_WAY_DESC", clearCardId.trim(), "=", false));
		}
		//直属代理商
		if(Constance.isNotEmpty(directlyAgent)){
			filedBeanList.add(new WhereHQLFieldBean("OP.real_name", directlyAgent.trim(), "=", false));
		}
		//商户关联关系
		if(Constance.isNotEmpty(association)&&!"99".equals(association)){ 
			filedBeanList.add(new WhereHQLFieldBean("f.group_cd", association.trim(), "=", false));
		}
		
		//所属机构
		if(Constance.isNotEmpty(orgCode)){
			filedBeanList.add(new WhereHQLFieldBean("f.BANK_BELONG_CD", orgCode.trim(), "=", false));
		}
		
		if(Constance.isNotEmpty(merchantNo)){ //商户号
			filedBeanList.add(new WhereHQLFieldBean("f.MCHT_CD", merchantNo.trim(), "=", false));
		}
		 
		if(Constance.isNotEmpty(merBrName)){ //商户名称 （模糊）
			filedBeanList.add(new WhereHQLFieldBean("f.NAME", merBrName.trim(), "like", false,"ALL"));
		}
		
		if(Constance.isNotEmpty(merchantName)){ //商户名称 （模糊）
			filedBeanList.add(new WhereHQLFieldBean("f.NAME_BUSI", merchantName.trim(), "like", false,"ALL"));
		}
		 
		if(Constance.isNotEmpty(minDate)&&Constance.isNotEmpty(maxDate)){ //入网时间
			filedBeanList.add(new WhereHQLFieldBean("f.APPL_DATE",minDate.trim(),">=", false));
       	    filedBeanList.add(new WhereHQLFieldBean("f.APPL_DATE",maxDate.trim(),"<=", false));
		}

		if(Constance.isNotEmpty(certifName)){ //法人
			filedBeanList.add(new WhereHQLFieldBean("f.CERTIF", certifName.trim(), "like", false,"ALL"));
		}
		if(Constance.isNotEmpty(cardNo)){ //法人身份证号
			filedBeanList.add(new WhereHQLFieldBean("f.CERTIF_NO", cardNo.trim(), "=", false));
		}
		if(Constance.isNotEmpty(contactName)){ //业务联系人
			filedBeanList.add(new WhereHQLFieldBean("f.CONTACT_NAME", contactName.trim() ,"like", false,"ALL"));
		} 
		if(Constance.isNotEmpty(phone)){ //业务联系电话
			filedBeanList.add(new WhereHQLFieldBean("f.CONTACT_PHONENO", phone.trim(), "=", false));
		} 
		if(Constance.isNotEmpty(status) && !status.trim().equals("99")){ //状态
			filedBeanList.add(new WhereHQLFieldBean("f.STATUS", status.trim(), "=", false));
		}
		if(Constance.isNotEmpty(accountNo)){ //结算账号
			filedBeanList.add(new WhereHQLFieldBean("b.ACCOUNT", accountNo.trim(), "=", false));
		} 
		if(Constance.isNotEmpty(accountName)){ //结算账户名称
			filedBeanList.add(new WhereHQLFieldBean("b.NAME", accountName.trim() ,"like", false,"ALL"));
		}
		

		if(Constance.isNotEmpty(upMccCd)){ // 商户MCC(42)
			filedBeanList.add(new WhereHQLFieldBean("f.UP_MCC_CD", upMccCd.trim(), "=", false));
		}
		if(Constance.isNotEmpty(busiLiceNo)){ // 营业执照号码
			filedBeanList.add(new WhereHQLFieldBean("f.BUSI_LICE_NO", busiLiceNo.trim(), "=", false));
		}
		if(Constance.isNotEmpty(busLicCode) && !"-1".equals(busLicCode)){ //审核人员Id
			filedBeanList.add(new WhereHQLFieldBean("f.ORI_CHNL_DESC", busLicCode.trim() ,"=", false));
		} 
		
		if(Constance.isNotEmpty(areaCode) && !"-1".equals(areaCode)){ // 区域id
			filedBeanList.add(new WhereHQLFieldBean("OP.COMPANYID", areaCode.trim(), "=", false));
		}
		if(Constance.isNotEmpty(sonCode) && !"-1".equals(sonCode)){ // 一级代理商id
			filedBeanList.add(new WhereHQLFieldBean("f.uc_bc_cd_area", sonCode.trim() ,"=", false));
		} 
		
		return filedBeanList;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getObj() {
		return obj;
	}

	public void setObj(String obj) {
		this.obj = obj;
	}

	public String getCrtMinDate() {
		return crtMinDate;
	}

	public void setCrtMinDate(String crtMinDate) {
		this.crtMinDate = crtMinDate;
	}

	public String getCrtMaxDate() {
		return crtMaxDate;
	}

	public void setCrtMaxDate(String crtMaxDate) {
		this.crtMaxDate = crtMaxDate;
	}

	public String getMerchantNo() {
		return merchantNo;
	}

	public void setMerchantNo(String merchantNo) {
		this.merchantNo = merchantNo;
	}

	public String getMerchantName() {
		return merchantName;
	}

	public void setMerchantName(String merchantName) {
		this.merchantName = merchantName;
	}

	public String getMerBrName() {
		return merBrName;
	}

	public void setMerBrName(String merBrName) {
		this.merBrName = merBrName;
	}

	public String getCertifName() {
		return certifName;
	}

	public void setCertifName(String certifName) {
		this.certifName = certifName;
	}

	public String getCardNo() {
		return cardNo;
	}

	public void setCardNo(String cardNo) {
		this.cardNo = cardNo;
	}

	public String getContactName() {
		return contactName;
	}

	public void setContactName(String contactName) {
		this.contactName = contactName;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getAccountNo() {
		return accountNo;
	}

	public void setAccountNo(String accountNo) {
		this.accountNo = accountNo;
	}

	public String getAccountName() {
		return accountName;
	}

	public void setAccountName(String accountName) {
		this.accountName = accountName;
	}

	public String getClearCardId() {
		return clearCardId;
	}

	public void setClearCardId(String clearCardId) {
		this.clearCardId = clearCardId;
	}

	public String getDirectlyAgent() {
		return directlyAgent;
	}

	public void setDirectlyAgent(String directlyAgent) {
		this.directlyAgent = directlyAgent;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getAssociation() {
		return association;
	}

	public void setAssociation(String association) {
		this.association = association;
	}

	public String getParentCode() {
		return parentCode;
	}

	public void setParentCode(String parentCode) {
		this.parentCode = parentCode;
	}

	public String getUpMccCd() {
		return upMccCd;
	}

	public void setUpMccCd(String upMccCd) {
		this.upMccCd = upMccCd;
	}

	public String getBusiLiceNo() {
		return busiLiceNo;
	}

	public void setBusiLiceNo(String busiLiceNo) {
		this.busiLiceNo = busiLiceNo;
	}

	public String getBusLicCode() {
		return busLicCode;
	}

	public void setBusLicCode(String busLicCode) {
		this.busLicCode = busLicCode;
	}

	public String getAreaCode() {
		return areaCode;
	}

	public void setAreaCode(String areaCode) {
		this.areaCode = areaCode;
	}

	public String getSonCode() {
		return sonCode;
	}

	public void setSonCode(String sonCode) {
		this.sonCode = sonCode;
	}

}
